package com.dx.mes.web;

import com.dx.common.table.ppt.RetSampDef;
import com.dx.common.table.ppt.RetSampTplDef;
import com.dx.common.table.ppt.RetSlotDef;
import com.dx.mes.common.transcoder.util.JacksonUtil;
import com.dx.mes.facade.special.service.IFspsmpdefService;
import com.dx.mes.facade.special.service.IFspsmprlnService;
import com.dx.mes.facade.special.tx.Fspsmpdef.FspsmpdefI;
import com.dx.mes.facade.special.tx.Fspsmpdef.Fspsmpdefexport;
import com.dx.mes.facade.special.tx.Fspsmprln.FspsmprlnI;
import com.dx.mes.facade.special.tx.Fspsmprln.FspsmprlnR;
import com.dx.mes.facade.special.tx.Fspsmprln.Fspsmprlnimport;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
import org.springframework.web.servlet.ModelAndView;

import javax.annotation.Resource;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by tl on 2017/7/12.
 */
@Controller
public class FspfutactionController {

    @Resource
    private IFspsmprlnService fspsmprlnService;

    @Resource
    private IFspsmpdefService fspsmpdefService;

    //导出模版下载
    @RequestMapping(value = "/download.do")
    public String downloadFile(@RequestParam("fileName") String fileName,
                               HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException {

        if (fileName != null) {
            File file = new File("/opt/icim_dx/excel/" + fileName);
            if (file.exists()) {
                response.setContentType("application/force-download");
                response.addHeader("Content-Disposition",
                        "attachment;fileName=" + fileName);
                byte[] buffer = new byte[1024];
                FileInputStream fis = null;
                BufferedInputStream bis = null;
                try {
                    fis = new FileInputStream(file);
                    bis = new BufferedInputStream(fis);
                    OutputStream os = response.getOutputStream();
                    int i = bis.read(buffer);
                    while (i != -1) {
                        os.write(buffer, 0, i);
                        i = bis.read(buffer);
                    }
                } catch (Exception e) {
                    // TODO: handle exception
                    e.printStackTrace();
                } finally {
                    if (bis != null) {
                        try {
                            bis.close();
                        } catch (IOException e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }
                    if (fis != null) {
                        try {
                            fis.close();
                        } catch (IOException e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
        return null;
    }
    //2800导入
    @RequestMapping(value = "/upload.do")
    public void upload(@RequestParam(value = "file", required = false) CommonsMultipartFile file,
                               Model model, HttpServletResponse response, HttpServletRequest request) throws IOException, ServletException {
        String filename = file.getOriginalFilename();
        InputStream fileInputStream = file.getInputStream();
        //4,存入数组
        ArrayList<String> arrayList = new ArrayList<>();
        try {
            boolean is03Excel = filename.matches("^.+\\.(?i)(xls)$");
            //1、读取工作簿
            Workbook workbook = is03Excel ? new HSSFWorkbook(fileInputStream) : new XSSFWorkbook(fileInputStream);
            //2、读取工作表
            Sheet sheet = workbook.getSheetAt(0);

            //3、读取行
            if (sheet.getPhysicalNumberOfRows() > 1) {
                for (int k = 1; k < sheet.getPhysicalNumberOfRows(); k++) {
                    //4、读取单元格
                    Row row = sheet.getRow(k);
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell0 = row.getCell(0);
                    arrayList.add(cell0.getStringCellValue());
                }
            }
            fileInputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        request.setAttribute("list",arrayList);
        request.getRequestDispatcher("page/2000/2800.jsp").forward(request,response);
    }

    //2910导出
    @RequestMapping("/exportForm.do")
    public void export(HttpServletResponse response,@RequestParam("tpl_seq_id") String act_seq_id){
        try {
            //导出三部曲
            response.setContentType("application/x-execl");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String("SampRelation_Temple.xls".getBytes(), "ISO-8859-1"));
            ServletOutputStream outputStream = response.getOutputStream();

            FspsmprlnI fspsmprlnI = new FspsmprlnI();
            fspsmprlnI.setAct_seq_id(act_seq_id);
            String s = JacksonUtil.toJSONStr(fspsmprlnI);
            //首先查询要导出的列表
            FspsmprlnR fspsmprlnR = fspsmprlnService.export(s);
            String tpl_seq_id = fspsmprlnR.getTpl_seq_id();
            List<RetSlotDef> retSlotDefList = fspsmprlnR.getRetSlotDefList();

            //构建excel
            //1.创建工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //2.创建工作表
            HSSFSheet sheet = workbook.createSheet("slot");
            //设置列宽
            sheet.setDefaultColumnWidth(25);
            //3.创建行
            HSSFRow row = sheet.createRow(0);
            String[] titles={"取样关系序列号","Slot选择方式","Slot号"};
            for(int i=0;i<titles.length;i++){
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(titles[i]);
            }
            //4.操作单元格，写入excel
            if (retSlotDefList != null && retSlotDefList.size() > 0) {
                for (int i = 0; i < retSlotDefList.size(); i++) {
                    RetSlotDef retSlotDef = retSlotDefList.get(i);
                    HSSFRow row1 = sheet.createRow(i + 1);

                    HSSFCell cell = row1.createCell(0);
                    cell.setCellValue(tpl_seq_id);


                    HSSFCell cell1 = row1.createCell(1);
                    cell1.setCellValue(retSlotDef.getSlot_get_method());

                    HSSFCell cell2 = row1.createCell(2);
                    cell2.setCellValue(retSlotDef.getSlot_no());
                }
            }
            //2.创建工作表2
            //主表信息
            RetSampTplDef retSampTplDef = fspsmprlnR.getRetSampTplDef();
            HSSFSheet sheet1 = workbook.createSheet("relation");
            //设置列宽
            sheet1.setDefaultColumnWidth(15);
            //3.创建行
            HSSFRow row1 = sheet1.createRow(0);
            String[] titles1={"取样关系序列号","模板ID","产品代码","工艺路线","工艺版本","操作代码","取样工艺路线","取样工艺版本","备注"};
            for(int i=0;i<titles1.length;i++){
                HSSFCell cell = row1.createCell(i);
                cell.setCellValue(titles1[i]);
            }
            //4.操作单元格，写入excel
            if (retSampTplDef != null) {

                HSSFRow row2 = sheet1.createRow(1);
                if (StringUtils.isNotBlank(retSampTplDef.getTpl_seq_id())) {
                    HSSFCell cell = row2.createCell(0);
                    cell.setCellValue(retSampTplDef.getTpl_seq_id());
                }
                if (StringUtils.isNotBlank(retSampTplDef.getSamp_tpl_id())) {
                    HSSFCell cell = row2.createCell(1);
                    cell.setCellValue(retSampTplDef.getSamp_tpl_id());
                }
                if (StringUtils.isNotBlank(retSampTplDef.getMdl_id_fk())) {
                    HSSFCell cell = row2.createCell(2);
                    cell.setCellValue(retSampTplDef.getMdl_id_fk());
                }
                if (StringUtils.isNotBlank(retSampTplDef.getPath_id_fk())) {
                    HSSFCell cell = row2.createCell(3);
                    cell.setCellValue(retSampTplDef.getPath_id_fk());
                }
                if (StringUtils.isNotBlank(retSampTplDef.getPath_ver_fk())) {
                    HSSFCell cell = row2.createCell(4);
                    cell.setCellValue(retSampTplDef.getPath_ver_fk());
                }
                if (StringUtils.isNotBlank(retSampTplDef.getOpe_no_fk())) {
                    HSSFCell cell = row2.createCell(5);
                    cell.setCellValue(retSampTplDef.getOpe_no_fk());
                }
                if (StringUtils.isNotBlank(retSampTplDef.getSamp_path_id_fk())) {
                    HSSFCell cell = row2.createCell(6);
                    cell.setCellValue(retSampTplDef.getSamp_path_id_fk());
                }
                if (StringUtils.isNotBlank(retSampTplDef.getSamp_path_ver_fk())) {
                    HSSFCell cell = row2.createCell(7);
                    cell.setCellValue(retSampTplDef.getSamp_path_ver_fk());
                }
                if (StringUtils.isNotBlank(retSampTplDef.getEvt_note())) {
                    HSSFCell cell = row2.createCell(8);
                    cell.setCellValue(retSampTplDef.getEvt_note());
                }

            }
            workbook.write(outputStream);
            outputStream.close();

        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    //2910导入
    @RequestMapping(value = "/uploadRList.do")
    public ModelAndView uploadRList(@RequestParam(value = "file", required = false) CommonsMultipartFile file,
                                   Model model, HttpServletResponse response, HttpServletRequest request) throws IOException, ServletException {
        ModelAndView modelAndView = new ModelAndView();
        modelAndView.setViewName("resultTpl");
        String filename = file.getOriginalFilename();
        InputStream fileInputStream = file.getInputStream();
        try {
            boolean is03Excel = filename.matches("^.+\\.(?i)(xls)$");
            //1、读取工作簿
            Workbook workbook = is03Excel ? new HSSFWorkbook(fileInputStream) : new XSSFWorkbook(fileInputStream);
            //2、读取工作表(slot)
            Sheet sheet = workbook.getSheetAt(0);

            //3、读取行
            ArrayList<RetSlotDef> retSlotDefs = new ArrayList<>();
            if (sheet.getPhysicalNumberOfRows() > 1) {
                for (int k = 1; k < sheet.getPhysicalNumberOfRows(); k++) {
                    RetSlotDef retSlotDef = new RetSlotDef();
                    //4、读取单元格
                    Row row = sheet.getRow(k);
                    if (row.getCell(0) != null) {
                        retSlotDef.setAct_seq_id(row.getCell(0).getStringCellValue());
                    }
                    if (row.getCell(1) != null) {
                        row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        retSlotDef.setSlot_get_method(Integer.parseInt(row.getCell(1).getStringCellValue()));
                    }
                    if (row.getCell(2) != null) {
                        row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                        retSlotDef.setSlot_no(row.getCell(2).getStringCellValue());
                    }

                    retSlotDefs.add(retSlotDef);
                }
            }
                Sheet sheet1 = workbook.getSheetAt(1);

                //3、读取行
                ArrayList<RetSampTplDef> RetSampTplDefs = new ArrayList<>();
                if (sheet1.getPhysicalNumberOfRows() > 1) {
                    for (int k = 1; k < sheet1.getPhysicalNumberOfRows(); k++) {
                        RetSampTplDef retSampTplDef = new RetSampTplDef();
                        //4、读取单元格
                        Row row = sheet1.getRow(k);
                        if (row.getCell(0) != null) {
                            retSampTplDef.setTpl_seq_id(row.getCell(0).getStringCellValue());
                        }
                        if (row.getCell(1) != null) {
                            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                            retSampTplDef.setSamp_tpl_id(row.getCell(1).getStringCellValue());
                        }
                        if (row.getCell(2) != null) {
                            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                            retSampTplDef.setMdl_id_fk(row.getCell(2).getStringCellValue());
                        }
                        if (row.getCell(2) != null) {
                            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                            retSampTplDef.setMdl_id_fk(row.getCell(2).getStringCellValue());
                        }
                        if (row.getCell(3) != null) {
                            row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                            retSampTplDef.setPath_id_fk(row.getCell(3).getStringCellValue());
                        }
                        if (row.getCell(4) != null) {
                            row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                            retSampTplDef.setPath_ver_fk(row.getCell(4).getStringCellValue());
                        }
                        if (row.getCell(5) != null) {
                            row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                            retSampTplDef.setOpe_no_fk(row.getCell(5).getStringCellValue());
                        }
                        if (row.getCell(6) != null) {
                            row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
                            retSampTplDef.setSamp_path_id_fk(row.getCell(6).getStringCellValue());
                        }
                        if (row.getCell(7) != null) {
                            row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
                            retSampTplDef.setSamp_path_ver_fk(row.getCell(7).getStringCellValue());
                        }
                        if (row.getCell(8) != null) {
                            row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
                            retSampTplDef.setEvt_note(row.getCell(8).getStringCellValue());
                        }


                        RetSampTplDefs.add(retSampTplDef);
                    }
                Fspsmprlnimport fspsmprlnimport = fspsmprlnService.importExcel(retSlotDefs,RetSampTplDefs);
                modelAndView.addObject("obj",fspsmprlnimport);
            }
            fileInputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return modelAndView;
    }


    //2920导出
    @RequestMapping("/exportList.do")
    public void exportList(HttpServletResponse response,@RequestParam("samp_seq_id") String samp_seq_id){
        try {
            //导出三部曲
            response.setContentType("application/x-execl");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String("Sampling_Temple.xls".getBytes(), "ISO-8859-1"));
            ServletOutputStream outputStream = response.getOutputStream();

            FspsmpdefI fspsmpdefI = new FspsmpdefI();
            fspsmpdefI.setSamp_seq_id(samp_seq_id);
            String s = JacksonUtil.toJSONStr(fspsmpdefI);
            //首先查询要导出的列表
            List<RetSampDef> retSlotDefList = fspsmpdefService.export(s);
            //构建excel
            //1.创建工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //2.创建工作表
            HSSFSheet sheet = workbook.createSheet("Sampling_Temple");
            //设置列宽
            sheet.setDefaultColumnWidth(15);
            //3.创建行
            HSSFRow row = sheet.createRow(0);
            String[] titles={"激活标识","制程代码","制程版本","设备代码","产品代码","工艺路线","工艺版本","操作代码","模板ID","间隔计数","取样批数","备注"};
            for(int i=0;i<titles.length;i++){
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(titles[i]);
            }
            //4.操作单元格，写入excel
            if (retSlotDefList != null && retSlotDefList.size() > 0) {
                for (int i = 0; i < retSlotDefList.size(); i++) {
                    RetSampDef retSampDef = retSlotDefList.get(i);
                    HSSFRow row1 = sheet.createRow(i + 1);

                    HSSFCell cell = row1.createCell(0);
                    cell.setCellValue(retSampDef.getValid_flg());


                    HSSFCell cell1 = row1.createCell(1);
                    cell1.setCellValue(retSampDef.getOpe_id_fk());

                    HSSFCell cell2 = row1.createCell(2);
                    cell2.setCellValue(retSampDef.getOpe_ver_fk());

                    HSSFCell cell3 = row1.createCell(3);
                    cell3.setCellValue(retSampDef.getTool_id_fk());

                    HSSFCell cell4 = row1.createCell(4);
                    cell4.setCellValue(retSampDef.getMdl_id_fk());

                    HSSFCell cell5 = row1.createCell(5);
                    cell5.setCellValue(retSampDef.getPath_id_fk());

                    HSSFCell cell6 = row1.createCell(6);
                    cell6.setCellValue(retSampDef.getPath_ver_fk());

                    HSSFCell cell7 = row1.createCell(7);
                    cell7.setCellValue(retSampDef.getOpe_no_fk());

                    HSSFCell cell8 = row1.createCell(8);
                    cell8.setCellValue(retSampDef.getSamp_tpl_id_fk());

                    if (retSampDef.getSamp_qty() != null) {
                        HSSFCell cell9 = row1.createCell(9);
                        cell9.setCellValue(retSampDef.getSamp_qty());
                    } else {
                        HSSFCell cell9 = row1.createCell(9);
                        cell9.setCellValue(0);
                    }
                    if (retSampDef.getRsv_batch_qty() != null) {
                        HSSFCell cell10 = row1.createCell(10);
                        cell10.setCellValue(retSampDef.getRsv_batch_qty());
                    } else {
                        HSSFCell cell10 = row1.createCell(10);
                        cell10.setCellValue(0);
                    }
                    HSSFCell cell11 = row1.createCell(11);
                    cell11.setCellValue(retSampDef.getCrt_note());
                }
            }
            workbook.write(outputStream);
            outputStream.close();

        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    //2920导入
    @RequestMapping(value = "/uploadList.do")
    public ModelAndView uploadList(@RequestParam(value = "file", required = false) CommonsMultipartFile file,
                                   Model model, HttpServletResponse response, HttpServletRequest request) throws IOException, ServletException {
        ModelAndView modelAndView = new ModelAndView();
        modelAndView.setViewName("result");
        String filename = file.getOriginalFilename();
        InputStream fileInputStream = file.getInputStream();
        //4,存入数组
        ArrayList<String> arrayList = new ArrayList<>();
        try {
            boolean is03Excel = filename.matches("^.+\\.(?i)(xls)$");
            //1、读取工作簿
            Workbook workbook = is03Excel ? new HSSFWorkbook(fileInputStream) : new XSSFWorkbook(fileInputStream);
            //2、读取工作表
            Sheet sheet = workbook.getSheetAt(0);

            //3、读取行
            ArrayList<RetSampDef> retSampDefs = new ArrayList<>();
            if (sheet.getPhysicalNumberOfRows() > 1) {
                for (int k = 1; k < sheet.getPhysicalNumberOfRows(); k++) {
                    RetSampDef retSampDef = new RetSampDef();
                    //4、读取单元格
                    Row row = sheet.getRow(k);
                    if (row.getCell(0) != null) {
                        retSampDef.setValid_flg(row.getCell(0).getStringCellValue());
                    }
                    if (row.getCell(1) != null) {
                        row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        retSampDef.setOpe_id_fk(row.getCell(1).getStringCellValue());
                    }
                    if (row.getCell(2) != null) {
                        row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                        retSampDef.setOpe_ver_fk(row.getCell(2).getStringCellValue());
                    }
                    if (row.getCell(3) != null) {
                        row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                        retSampDef.setTool_id_fk(row.getCell(3).getStringCellValue());
                    }
                    if (row.getCell(4) != null) {
                        row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                        retSampDef.setMdl_id_fk(row.getCell(4).getStringCellValue());
                    }
                    if (row.getCell(5) != null) {
                        row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                        retSampDef.setPath_id_fk(row.getCell(5).getStringCellValue());
                    }
                    if (row.getCell(6) != null) {
                        row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
                        retSampDef.setPath_ver_fk(row.getCell(6).getStringCellValue());
                    }
                    if (row.getCell(7) != null) {
                        row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
                        retSampDef.setOpe_no_fk(row.getCell(7).getStringCellValue());
                    }
                    if (row.getCell(8) != null) {
                        row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
                        retSampDef.setSamp_tpl_id_fk(row.getCell(8).getStringCellValue());
                    }
                    if (row.getCell(9) != null) {
                        row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
                        retSampDef.setSamp_qty(Integer.parseInt(row.getCell(9).getStringCellValue()));
                    } else {
                        retSampDef.setSamp_qty(0);
                    }
                    if (row.getCell(10) != null) {
                        row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
                        retSampDef.setRsv_batch_qty(Integer.parseInt(row.getCell(10).getStringCellValue()));
                    } else {
                        retSampDef.setRsv_batch_qty(0);
                    }
                    if (row.getCell(11) != null) {
                        row.getCell(11).setCellType(Cell.CELL_TYPE_STRING);
                        retSampDef.setCrt_note(row.getCell(11).getStringCellValue());
                    }
                    retSampDefs.add(retSampDef);
                }
                Fspsmpdefexport fspsmpdefexport = fspsmpdefService.insertBeanWithVerify(retSampDefs);
                modelAndView.addObject("obj",fspsmpdefexport);
            }
            fileInputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return modelAndView;
    }



















}
